const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class CalendarDatabase {
    constructor(db) {
        this.db = db;
    }

    async createEventsTable() {
        const createEventsTable = `
            CREATE TABLE IF NOT EXISTS calendar_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                widget_id TEXT NOT NULL,
                title TEXT NOT NULL,
                date DATETIME NOT NULL,
                type TEXT NOT NULL DEFAULT 'meeting',
                description TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (widget_id) REFERENCES widgets (id) ON DELETE CASCADE
            )
        `;
        
        return new Promise((resolve, reject) => {
            this.db.run(createEventsTable, (err) => {
                if (err) {
                    reject(err);
                } else {
                    resolve();
                }
            });
        });
    }

    async addEvent(widgetId, title, date, type = 'meeting', description = '') {
        return new Promise((resolve, reject) => {
            const stmt = this.db.prepare(`
                INSERT INTO calendar_events (widget_id, title, date, type, description)
                VALUES (?, ?, ?, ?, ?)
            `);
            
            stmt.run([widgetId, title, date, type, description], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        id: this.lastID.toString(),
                        title,
                        date,
                        type,
                        description,
                        createdAt: new Date().toISOString()
                    });
                }
            });
            stmt.finalize();
        });
    }

    async getEvents(widgetId) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT * FROM calendar_events WHERE widget_id = ? ORDER BY date ASC',
                [widgetId],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const events = rows.map(row => ({
                            id: row.id.toString(),
                            title: row.title,
                            date: row.date,
                            type: row.type,
                            description: row.description,
                            createdAt: row.created_at
                        }));
                        resolve(events);
                    }
                }
            );
        });
    }

    async getEventsForDate(widgetId, date) {
        const startDate = new Date(date);
        startDate.setHours(0, 0, 0, 0);
        const endDate = new Date(date);
        endDate.setHours(23, 59, 59, 999);
        
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT * FROM calendar_events WHERE widget_id = ? AND date BETWEEN ? AND ? ORDER BY date ASC',
                [widgetId, startDate.toISOString(), endDate.toISOString()],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const events = rows.map(row => ({
                            id: row.id.toString(),
                            title: row.title,
                            date: row.date,
                            type: row.type,
                            description: row.description,
                            createdAt: row.created_at
                        }));
                        resolve(events);
                    }
                }
            );
        });
    }

    async getUpcomingEvents(widgetId, days = 7) {
        const now = new Date();
        const future = new Date(now.getTime() + days * 24 * 60 * 60 * 1000);
        
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT * FROM calendar_events WHERE widget_id = ? AND date BETWEEN ? AND ? ORDER BY date ASC',
                [widgetId, now.toISOString(), future.toISOString()],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const events = rows.map(row => ({
                            id: row.id.toString(),
                            title: row.title,
                            date: row.date,
                            type: row.type,
                            description: row.description,
                            createdAt: row.created_at
                        }));
                        resolve(events);
                    }
                }
            );
        });
    }

    async deleteEvent(eventId) {
        return new Promise((resolve, reject) => {
            this.db.run('DELETE FROM calendar_events WHERE id = ?', [eventId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    async updateEvent(eventId, updates) {
        const fields = [];
        const values = [];
        
        if (updates.title) {
            fields.push('title = ?');
            values.push(updates.title);
        }
        if (updates.date) {
            fields.push('date = ?');
            values.push(updates.date);
        }
        if (updates.type) {
            fields.push('type = ?');
            values.push(updates.type);
        }
        if (updates.description !== undefined) {
            fields.push('description = ?');
            values.push(updates.description);
        }
        
        if (fields.length === 0) {
            return false;
        }
        
        fields.push('updated_at = CURRENT_TIMESTAMP');
        values.push(eventId);
        
        return new Promise((resolve, reject) => {
            this.db.run(
                `UPDATE calendar_events SET ${fields.join(', ')} WHERE id = ?`,
                values,
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    async getEventsByType(widgetId, type) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT * FROM calendar_events WHERE widget_id = ? AND type = ? ORDER BY date ASC',
                [widgetId, type],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const events = rows.map(row => ({
                            id: row.id.toString(),
                            title: row.title,
                            date: row.date,
                            type: row.type,
                            description: row.description,
                            createdAt: row.created_at
                        }));
                        resolve(events);
                    }
                }
            );
        });
    }

    async getEventStats(widgetId) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT type, COUNT(*) as count FROM calendar_events WHERE widget_id = ? GROUP BY type',
                [widgetId],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const stats = { total: 0, byType: {} };
                        rows.forEach(row => {
                            stats.total += row.count;
                            stats.byType[row.type] = row.count;
                        });
                        resolve(stats);
                    }
                }
            );
        });
    }
}

module.exports = CalendarDatabase;